1 drop table if exists user_choice
;
2 drop table if exists user_choice_buffer_anonymous
;
3 drop table if exists user_vote
;
4 drop table if exists choices
;
5 drop table if exists cardinals
;
6 drop table if exists values_
;
7 drop table if exists attachments
;
8 drop table if exists votes
;
9 drop table if exists user_group
;
10 drop table if exists groups
;
11 drop table if exists users
;
13 PRAGMA foreign_keys
= ON;
16 id INTEGER PRIMARY KEY AUTOINCREMENT
,
17 email
TEXT UNIQUE NOT NULL,
18 password TEXT NOT NULL,
20 name TEXT UNIQUE NOT NULL,
22 is_admin
BOOLEAN DEFAULT 0 NOT NULL,
24 CHECK (is_admin
IN (0, 1))
28 id INTEGER PRIMARY KEY AUTOINCREMENT
,
29 name TEXT UNIQUE NOT NULL,
30 system BOOLEAN DEFAULT 0 NOT NULL,
31 CHECK (system IN (0, 1))
34 create table user_group (
37 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
38 FOREIGN KEY(id_group
) REFERENCES groups (id) ON DELETE CASCADE,
39 PRIMARY KEY(id_user
, id_group
)
42 create table cardinals (
43 id INTEGER UNIQUE NOT NULL PRIMARY KEY,
44 name TEXT UNIQUE NOT NULL,
48 create table values_ (
50 weight
INTEGER NOT NULL,
51 id_cardinal
INTEGER NOT NULL,
52 FOREIGN KEY(id_cardinal
) REFERENCES cardinals (id) ON DELETE CASCADE,
53 PRIMARY KEY(id_cardinal
, weight
)
57 id INTEGER PRIMARY KEY AUTOINCREMENT
,
61 date_begin
INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
62 date_end
INTEGER NOT NULL,
63 quorum
FLOAT DEFAULT 1,
64 reminder_last_days
INTEGER DEFAULT 3 NOT NULL,
65 is_transparent
BOOLEAN DEFAULT 1 NOT NULL,
66 is_public
BOOLEAN DEFAULT 1 NOT NULL,
67 is_anonymous
BOOLEAN DEFAULT 1 NOT NULL,
68 is_open
BOOLEAN DEFAULT 0 NOT NULL,
69 is_terminated
BOOLEAN DEFAULT 0 NOT NULL,
70 is_hidden
BOOLEAN DEFAULT 0 NOT NULL,
71 id_author
INTEGER DEFAULT 1 NOT NULL,
72 id_group
INTEGER DEFAULT 1 NOT NULL,
73 id_cardinal
INTEGER NOT NULL,
74 FOREIGN KEY(id_author
) REFERENCES users (id) ON DELETE SET DEFAULT,
75 FOREIGN KEY(id_group
) REFERENCES groups (id),
76 FOREIGN KEY(id_cardinal
) REFERENCES cardinals (id),
77 CHECK (is_transparent
IN (0, 1)),
78 CHECK (is_public
IN (0, 1)),
79 CHECK (is_open
IN (0, 1)),
80 CHECK (is_terminated
IN (0, 1)),
81 CHECK (is_hidden
IN (0, 1))
84 create table attachments (
85 id INTEGER PRIMARY KEY AUTOINCREMENT
,
87 id_vote
INTEGER NOT NULL,
88 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE
91 create table choices (
92 id INTEGER PRIMARY KEY AUTOINCREMENT
,
94 id_vote
INTEGER NOT NULL,
95 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE
98 create table user_choice (
100 id_choice
INTEGER NOT NULL,
103 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
104 FOREIGN KEY(id_choice
) REFERENCES choices (id) ON DELETE CASCADE,
105 FOREIGN KEY(id_cardinal
, weight
) REFERENCES values_ (id_cardinal
, weight
) ON DELETE CASCADE,
106 PRIMARY KEY(id_user
, id_choice
)
109 create table user_choice_buffer_anonymous (
110 -- NOTE: same table structure as user_choice, used to randomize insertion per vote
112 id_choice
INTEGER NOT NULL,
115 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
116 FOREIGN KEY(id_choice
) REFERENCES choices (id) ON DELETE CASCADE,
117 FOREIGN KEY(id_cardinal
, weight
) REFERENCES values_ (id_cardinal
, weight
) ON DELETE CASCADE,
118 PRIMARY KEY(id_user
, id_choice
)
121 create table user_vote (
122 date INTEGER DEFAULT CURRENT_TIMESTAMP NOT NULL,
123 id_user
INTEGER NOT NULL,
124 id_vote
INTEGER NOT NULL,
126 FOREIGN KEY(id_user
) REFERENCES users (id) ON DELETE CASCADE,
127 FOREIGN KEY(id_vote
) REFERENCES votes (id) ON DELETE CASCADE,
128 PRIMARY KEY(id_user
, id_vote
)